package com.wanmait.movies.dao;

import java.security.Timestamp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import com.wanmait.movies.util.DBHelper;
import com.wanmait.movies.vo.Actor;
import com.wanmait.movies.vo.AddMovieRecords;
import com.wanmait.movies.vo.Collect;
import com.wanmait.movies.vo.DeleteMovieRecords;
import com.wanmait.movies.vo.Director;
import com.wanmait.movies.vo.MonfinMovieRecords;
import com.wanmait.movies.vo.Movie;
import com.wanmait.movies.vo.MovieCountry;
import com.wanmait.movies.vo.MovieLanguage;
import com.wanmait.movies.vo.MovieType;

public class MovieDao {

	
	private MovieDao() {};
	private static MovieDao a=new MovieDao();

	public static MovieDao getMovieDao() {
		return a;
	}
	
	public ArrayList<Movie> getSearchMovieByType(String id, int page) {//搜索电影名，模糊查询所有电影
		ArrayList<Movie> movies = new ArrayList<>();
		Connection co =  DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb=new StringBuilder();
		/*
	    sb.append(" SELECT * FROM movie,movie_type,movie_actor,actor,director,movie_director,(SELECT AVG(grades) as y,movie_id FROM givegrades GROUP BY movie_id) AS x WHERE movie_type_id=" + id);
	    sb.append(" AND x.movie_id=movie.id ");
	    sb.append(" AND movie_actor.actor_id =actor.id ");
		sb.append(" AND movie.id=movie_director.movie_id ");
		sb.append(" AND movie.id=movie_actor.movie_id ");
		sb.append(" AND director.id=movie_director.director_id ");
	    sb.append("  AND movie.movie_type_id=movie_type.id");
	    sb.append(" ORDER BY movie_showtime DESC limit ");
	    sb.append(page);
	    sb.append(" , 8");
	    */

		sb.append("	SELECT * FROM (SELECT  x,movie_id FROM (select AVG(grades) AS x,movie_id FROM givegrades GROUP BY movie_id) AS y ,movie    ");
		sb.append(" WHERE movie.id = y.movie_id and movie_type_id ="+id+" limit "+(page-1)*8+",8) a ");
		sb.append(" left JOIN movie b ON b.id = a.movie_id  ");
		sb.append("	LEFT JOIN movie_actor c ON a.movie_id=c.movie_id  ");
		sb.append(" LEFT JOIN actor d ON c.actor_id=d.id ");
		sb.append(" LEFT JOIN movie_director e ON a.movie_id=e.movie_id ");
		sb.append(" LEFT JOIN director  f ON f.id=e.director_id ");
		sb.append(" left join movie_type g on g.id=b.movie_type_id");
		sb.append(" ORDER BY b.movie_showtime");
		Movie movie = null;
		ArrayList<Actor> actors = null;
		ArrayList<Director> directors = null;
		Actor actor = null;
		Director director = null;
		int i=0;
		try {
			ps = co.prepareStatement(sb.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				if (movie == null || movie.getId() != rs.getInt("b.id")) {
					i=0;
					movie = new Movie();
					actors = new ArrayList<Actor>();
					directors = new ArrayList<Director>();
					movies.add(movie);
					movie.setActorList(actors);
					movie.setDirectorList(directors);
					movie.setId(rs.getInt("b.id"));
					movie.setMovieImage(rs.getString("b.movie_image"));
					movie.setMovieTime(rs.getInt("b.movie_time"));
					movie.setMovieName(rs.getString("b.movie_name"));
					movie.setMovieGrades(rs.getDouble("x"));
					MovieType movieType = new MovieType();
					movieType.setMovieType(rs.getString("g.movie_type"));
					movie.setMovieTypeId(movieType);
					
					actor = null;
					director = null;
				}
				
				if (director == null || director.getId() != rs.getInt("f.id")) {
					director = new Director();
					director.setDirectorName(rs.getString("director_name"));
					director.setId(rs.getInt("f.id"));
					directors.add(director);
					i++;
				} 
				if (i==1) {
					actor = new Actor();
					actor.setActorName(rs.getString("actor_name"));
					actor.setId(rs.getInt("d.id"));
					actors.add(actor);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return movies;
		
	}
	public int pagemax() { //总页数
		Connection co=DBHelper.getDBHelper().getconn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select COUNT(id) AS a from movie";
		try {
			ps=co.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			int pagemax=rs.getInt("a");
		     return pagemax;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return 0;
	}
	public int pagemaxbytype(String key) { //总页数
		Connection co=DBHelper.getDBHelper().getconn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select COUNT(id) AS a from movie where movie_type_id = " + key;
		try {
			ps=co.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			int pagemax=rs.getInt("a");
			return pagemax;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return 0;
	}
	public ArrayList<Movie> thispagemovie(int thispage) {

		ArrayList<Movie> list = new ArrayList<Movie>();

		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "select * from movie limit ?,8";
		try {
			ps = co.prepareStatement(sql);
			ps.setInt(1, (thispage-1)*8);
			rs = ps.executeQuery();
			while (rs.next()) {
				Movie movie = new Movie();
				movie.setId(rs.getInt("id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieName(rs.getString("movie_name"));
				list.add(movie);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}

		return list;
	}
	public ArrayList<Movie> getSearchWillShowMovieList(int page) {//即将上映电影
		ArrayList<Movie> movies = new ArrayList<>();
		Connection co =  DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		/*
		sb.append(" SELECT * FROM  movie,movie_actor,actor,director,movie_director  ,(SELECT AVG(grades) as y,movie_id FROM givegrades GROUP BY movie_id) AS x WHERE ");
		sb.append(" movie.id = movie_actor.movie_id ");
		sb.append(" AND movie_actor.actor_id =actor.id ");
		sb.append(" AND movie.id=movie_director.movie_id ");
		sb.append(" AND director.id=movie_director.director_id ");
		sb.append(" AND x.movie_id=movie.id ");
		sb.append(" AND movie_show=0 ");
		sb.append(" ORDER BY movie_showtime "); 
		
		sb.append("	SELECT * FROM movie a ");
		sb.append(" left JOIN (SELECT AVG(grades) as x,movie_id FROM givegrades GROUP BY movie_id)  b ON a.id = b.movie_id ");
		sb.append("	LEFT JOIN movie_actor  c ON a.id=c.movie_id ");
		sb.append(" LEFT JOIN actor d ON c.actor_id=d.id ");
		sb.append(" LEFT JOIN movie_director e ON a.id=e.movie_id ");
		sb.append(" LEFT JOIN director  f ON f.id=e.director_id ");
		sb.append(" where movie_show=0");
		sb.append("  ORDER BY  movie_showtime desc,a.id ");
		sb.append(" limit ");
		sb.append((page-1)*8);
		sb.append(",8");
		*/
		 
		sb.append("	SELECT * FROM (SELECT  x,movie_id FROM (select round(AVG(grades),1) AS x,movie_id FROM givegrades GROUP BY movie_id) AS y ,movie    ");
		sb.append(" WHERE movie.id = y.movie_id and movie_show=0 limit "+(page-1)*8+",8) a ");
		sb.append(" left JOIN movie b ON b.id = a.movie_id  ");
		sb.append("	LEFT JOIN movie_actor c ON a.movie_id=c.movie_id  ");
		sb.append(" LEFT JOIN actor d ON c.actor_id=d.id ");
		sb.append(" LEFT JOIN movie_director e ON a.movie_id=e.movie_id ");
		sb.append(" LEFT JOIN director  f ON f.id=e.director_id ");
		sb.append(" left join movie_type g on g.id=b.movie_type_id");
		sb.append(" ORDER BY b.movie_showtime");
		 
		Movie movie = null;
		ArrayList<Actor> actors = null;
		ArrayList<Director> directors = null;
		Actor actor = null;
		Director director = null;
		int i=0;
		try {
			ps = co.prepareStatement(sb.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				if (movie == null || movie.getId() != rs.getInt("b.id")) {
					i=0;
					movie = new Movie();
					actors = new ArrayList<Actor>();
					directors = new ArrayList<Director>();
					movies.add(movie);
					movie.setActorList(actors);
					movie.setDirectorList(directors);
					movie.setId(rs.getInt("b.id"));
					movie.setMovieImage(rs.getString("b.movie_image"));
					movie.setMovieTime(rs.getInt("b.movie_time"));
					movie.setMovieName(rs.getString("b.movie_name"));
					movie.setMovieGrades(rs.getDouble("x"));
					
					actor = null;
				    director = null;
				}
				
				if (director == null || director.getId() != rs.getInt("f.id")) {
					director = new Director();
					director.setDirectorName(rs.getString("director_name"));
					director.setId(rs.getInt("f.id"));
					directors.add(director);
					i++;
				} 
				if (i==1) {
					actor = new Actor();
					actor.setActorName(rs.getString("actor_name"));
					actor.setId(rs.getInt("d.id"));
					actors.add(actor);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return movies;
		
	}
	public ArrayList<Movie> getSearchClickMovieList(int page) {//点赞电影
		ArrayList<Movie> movies = new ArrayList<>();
		Connection co =  DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		/*
		sb.append(" SELECT * FROM  movie,movie_actor,actor,director,movie_director ,(SELECT count(movie_id),movie_id FROM click GROUP BY movie_id ORDER BY COUNT(movie_id) desc) AS y, ");
		sb.append(" (SELECT AVG(grades) ,movie_id FROM givegrades GROUP BY movie_id) AS X ");
		sb.append(" where movie.id = movie_actor.movie_id ");
		sb.append(" AND movie_actor.actor_id =actor.id ");
		sb.append(" AND movie.id=movie_director.movie_id ");
		sb.append(" AND director.id=movie_director.director_id ");
		sb.append(" AND x.movie_id=movie.id");
		sb.append(" and y.movie_id=movie.id");
		*/
		
		sb.append("	SELECT * FROM (SELECT round(AVG(grades),1) AS x,movie_id FROM givegrades GROUP BY movie_id LIMIT ");
		sb.append((page-1)*8);
		sb.append(" ,8) a ");
		sb.append(" left JOIN movie b ON b.id = a.movie_id  ");
		sb.append("	LEFT JOIN movie_actor c ON a.movie_id=c.movie_id  ");
		sb.append(" LEFT JOIN actor d ON c.actor_id=d.id ");
		sb.append(" LEFT JOIN movie_director e ON a.movie_id=e.movie_id ");
		sb.append(" LEFT JOIN director  f ON f.id=e.director_id ");
		sb.append(" LEFT JOIN(SELECT count(movie_id) AS y,movie_id FROM click GROUP BY movie_id) g ON g.movie_id=a.movie_id ");
		sb.append("  ORDER BY g.y desc,a.x desc,a.movie_id ");
		Movie movie = null;
		ArrayList<Actor> actors = null;
		ArrayList<Director> directors = null;
		Actor actor = null;
		Director director = null;
		
		int i=0;
		try {
			ps = co.prepareStatement(sb.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				if (movie == null || movie.getId() != rs.getInt("b.id")) {
					i=0;
					movie = new Movie();
					actors = new ArrayList<Actor>();
					directors = new ArrayList<Director>();
					movies.add(movie);
					movie.setActorList(actors);
					movie.setDirectorList(directors);
					movie.setId(rs.getInt("b.id"));
					movie.setMovieImage(rs.getString("b.movie_image"));
					movie.setMovieTime(rs.getInt("b.movie_time"));
					movie.setMovieName(rs.getString("b.movie_name"));
					movie.setMovieGrades(rs.getDouble("x"));
					actor = null;
					 director = null;
				}
				
				if (director == null || director.getId() != rs.getInt("f.id")) {
					director = new Director();
					director.setDirectorName(rs.getString("director_name"));
					director.setId(rs.getInt("f.id"));
					directors.add(director);
					i++;
				} 
				if (i==1) {
					actor = new Actor();
					actor.setActorName(rs.getString("actor_name"));
					actor.setId(rs.getInt("d.id"));
					actors.add(actor);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return movies;
		
	}
	public ArrayList<Movie> getSearchGradesMovieList(int page) {//根据打分筛选电影
		ArrayList<Movie> movies = new ArrayList<>();
		Connection co =  DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		/*
		sb.append(" SELECT * FROM  movie,movie_actor,actor,director,movie_director  ,(SELECT AVG(grades) as y,movie_id FROM givegrades GROUP BY movie_id) AS x WHERE ");
		sb.append(" movie.id = movie_actor.movie_id ");
		sb.append(" AND movie_actor.actor_id =actor.id ");
		sb.append(" AND movie.id=movie_director.movie_id ");
		sb.append(" AND director.id=movie_director.director_id ");
		sb.append(" AND x.movie_id=movie.id order by y desc");
		*/
		 
		 	 
				
		sb.append("	SELECT * FROM (SELECT round(AVG(grades),1) AS x,movie_id FROM givegrades GROUP BY movie_id ORDER BY x desc LIMIT ");
		sb.append((page-1)*8);
		sb.append(" ,8) a ");
		sb.append(" left JOIN movie b ON b.id = a.movie_id  ");
		sb.append("	LEFT JOIN movie_actor c ON a.movie_id=c.movie_id  ");
		sb.append(" LEFT JOIN actor d ON c.actor_id=d.id ");
		sb.append(" LEFT JOIN movie_director e ON a.movie_id=e.movie_id ");
		sb.append(" LEFT JOIN director  f ON f.id=e.director_id ");
		sb.append("  ORDER BY a.x desc,a.movie_id ");
		Movie movie = null;
		ArrayList<Actor> actors = null;
		ArrayList<Director> directors = null;
		Actor 	actor = null;
		Director director = null;
		try {
			ps = co.prepareStatement(sb.toString());
			rs = ps.executeQuery();
			int i=0;
			while (rs.next()) {
				if (movie == null || movie.getId() != rs.getInt("b.id")) {
					i=0;
					movie = new Movie();
					actors = new ArrayList<Actor>();
					directors = new ArrayList<Director>();
					actor = null;
					director = null;
					movies.add(movie);
					movie.setActorList(actors); 
					movie.setDirectorList(directors);
					movie.setId(rs.getInt("b.id"));
					movie.setMovieImage(rs.getString("b.movie_image"));
					movie.setMovieTime(rs.getInt("b.movie_time"));
					movie.setMovieName(rs.getString("b.movie_name"));
					movie.setMovieGrades(rs.getDouble("x"));
					
				}
				if (director == null || director.getId() != rs.getInt("f.id")) {
					director = new Director();
					director.setDirectorName(rs.getString("director_name"));
					director.setId(rs.getInt("f.id"));
					directors.add(director);
					i++;
				} 
				if (i==1) {
					actor = new Actor();
					actor.setActorName(rs.getString("actor_name"));
					actor.setId(rs.getInt("d.id"));
					actors.add(actor);
				}
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return movies;
		
	}
	public ArrayList<Movie> getSearchMovieList(String searchword) {//搜索电影名，模糊查询所有电影
		ArrayList<Movie> movies = new ArrayList<>();
		Connection co =  DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		/*sb.append(" SELECT * FROM  movie,movie_actor,actor,director,movie_director ,(SELECT AVG(grades),movie_id FROM givegrades GROUP BY movie_id) AS x WHERE ");
		sb.append(" movie_name LIKE '%");
		sb.append(searchword);
		sb.append("%' AND movie.id = movie_actor.movie_id ");
		sb.append(" AND movie_actor.actor_id =actor.id ");
		sb.append(" AND movie.id=movie_director.movie_id ");
		sb.append(" AND director.id=movie_director.director_id ");
		sb.append(" AND x.movie_id=movie.id limit 10");
		 */
		//左连接
		 
		sb.append("	SELECT * FROM (SELECT round(AVG(grades),1) AS x,movie_id FROM givegrades GROUP BY movie_id  ");
		sb.append(" ) a ");
		sb.append(" left JOIN movie b ON b.id = a.movie_id  ");
		sb.append("	LEFT JOIN movie_actor c ON a.movie_id=c.movie_id  ");
		sb.append(" LEFT JOIN actor d ON c.actor_id=d.id ");
		sb.append(" LEFT JOIN movie_director e ON a.movie_id=e.movie_id ");
		sb.append(" LEFT JOIN director  f ON f.id=e.director_id ");
		sb.append(" WHERE b.movie_name LIKE '%");
		sb.append(searchword);
		sb.append("%' order by a.movie_id");
		 
		Movie movie = null;
		ArrayList<Actor> actors = null;
		ArrayList<Director> directors = null;
		Actor actor = null;
		Director director = null;
		
		int i=0;
		try {
			ps = co.prepareStatement(sb.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				if (movie == null || movie.getId() != rs.getInt("b.id")) {
					i=0;
					movie = new Movie();
					actors = new ArrayList<Actor>();
					directors = new ArrayList<Director>();
					movies.add(movie);
					movie.setActorList(actors);
					movie.setDirectorList(directors);
					movie.setId(rs.getInt("b.id"));
					movie.setMovieImage(rs.getString("b.movie_image"));
					movie.setMovieTime(rs.getInt("b.movie_time"));
					movie.setMovieName(rs.getString("b.movie_name"));
					movie.setMovieGrades(rs.getDouble("x"));
					 actor = null;
				     director = null;
				}
				if (director == null || director.getId() != rs.getInt("f.id")) {
					director = new Director();
					director.setDirectorName(rs.getString("director_name"));
					director.setId(rs.getInt("f.id"));
					directors.add(director);
					i++;
				} 
				if (i==1) {
					actor = new Actor();
					actor.setActorName(rs.getString("actor_name"));
					actor.setId(rs.getInt("d.id"));
					actors.add(actor);
				}
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return movies;
	
	}
	
	/*  
	public ArrayList<Movie> getSearchMovieList(String searchword) {//搜索电影名，模糊查询所有电影
		ArrayList<Movie> movies = new ArrayList<>();
		Connection co =  DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb=new StringBuilder();
        sb.append(" SELECT * FROM ");
        sb.append(" movie,movie_country,movie_language,movie_type ");
        sb.append(" WHERE movie_name LIKE '%"+searchword+"%' and ");
        sb.append(" movie_country.id=movie_country_id and ");
        sb.append(" movie_language_id=movie_language.id and ");
        sb.append(" movie_type_id=movie_type.id ");
        sb.append(" ORDER BY movie_showtime DESC  ");
        
        String sql=sb.toString();
		try {
			ps = co.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Movie movie=new Movie();
		         movie.setId(rs.getInt("movie.id"));//电影id
		         movie.setMovieName(rs.getString("movie_name"));//电影名
		         movie.setMovieTime(rs.getInt("movie_time"));//电影时长
		         
		         MovieType  c=new MovieType();//电影类型对象
		         c.setId(rs.getInt("movie_type_id"));//类型id
		         c.setMovieType(rs.getString("movie_type"));//类型名称
		         movie.setMovieTypeId(c);//将电影类型对象添加到电影对象
		         
		         movie.setMovieShowtime(rs.getTimestamp("movie_showtime"));//上映时间
		         
		         MovieCountry c2=new MovieCountry();//电影地区对象
		         c2.setId(rs.getInt("movie_country_id"));//地区id
		         c2.setCountry(rs.getString("country"));//地区名称
		         movie.setMovieCountryId(c2);//地区加到电影
		         
		         MovieLanguage c3=new MovieLanguage();//电影语言
		         c3.setId(rs.getInt("movie_language_id"));//语言id
		         c3.setLanguageType(rs.getString("language_type"));//语言类型
		         movie.setMovieLanguageId(c3);//语言加到电影
		         
		         movie.setMovieImage(rs.getString("movie_image"));//电影缩略图
		         movie.setMovieIntroduction(rs.getString("movie_introduction"));//电影简介
		         movie.setMovieShow(rs.getBoolean("movie_show"));//是否热映
		         
		         movie.setDirectorList(getDirectorsInAnMovie(movie.getId()));//拿到所有的导演
		         movie.setActorList(getActorsInAnMovie(movie.getId()));//拿到所有的演员
		         movie.setMovieGrades(getGradesInAnMovie(movie.getId()));
		         movies.add(movie);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return movies;
	}
	*/
	public Double getGradesInAnMovie(int id) {
		Double grades = null;
		 
		Connection co  = DBHelper.getDBHelper().getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
		try {
			ps = co.prepareStatement("SELECT round(AVG(grades),1) as x  FROM givegrades WHERE id = " + id);
			rs = ps.executeQuery();
			rs.next(); 	
			grades = rs.getDouble("x");	 
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		//System.out.println("查询电影分数方法执行" + grades);
		return grades;
	}
	public ArrayList<Director> getDirectorsInAnMovie(int id) {
		ArrayList<Director> directors = new ArrayList<>();
		 
		Connection co  = DBHelper.getDBHelper().getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
		try {
			ps = co.prepareStatement(
				 "select director_name from director,movie_director,movie "
				 + "WHERE movie.id=movie_director.movie_id "
				 + "and director.id=movie_director.director_id and movie.id = "+id
				 );
			rs = ps.executeQuery();
			while (rs.next()) {	
				Director director = new Director();
				director.setDirectorName(rs.getString("director_name"));
				directors.add(director);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		//System.out.println("查询电影导演方法执行" + directors.size());
		return directors;
	}
	public ArrayList<Actor> getActorsInAnMovie(int id) {
		ArrayList<Actor> actors = new ArrayList<>();
		Connection co  = DBHelper.getDBHelper().getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
		try {
			ps = co.prepareStatement(
				 "select actor_name from actor,movie_actor,movie "
				 + "WHERE movie.id=movie_actor.movie_id "
				 + "and actor.id=movie_actor.actor_id and movie.id ="+id
				 );
			rs = ps.executeQuery();
			while (rs.next()) {
				Actor actor = new Actor();
				actor.setActorName(rs.getString("actor_name"));
				actors.add(actor);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}//System.out.println("查询电影演员方法执行" + actors.size());
		return actors;
	}
	//拿到轮播图，只拿五个
	public Movie[] getLunBoMovies() {
	    Movie[] movies = new Movie[5];
	    int i = 0;
	    Connection co = DBHelper.getDBHelper().getconn();
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    String sql = " SELECT id, movie_poster fROM movie WHERE movie_show = 1 ORDER by movie_showtime deSC LIMIT 5 ";

	    
	    try {
	      ps = co.prepareStatement(sql);
	      rs = ps.executeQuery();
	      while (rs.next()) {
	        Movie movie = new Movie();
	        movie.setId(rs.getInt("id")); 
	        movie.setMoviePoster(rs.getString("movie_poster"));
	        movies[i] = movie;
	        i++;
	      }
	    } catch (SQLException e) {
	      // TODO Auto-generated catch block
	      e.printStackTrace();

	    } finally {

	      DBHelper.getDBHelper().close(rs, ps, co);
	    }
	    return movies;
	  }
	
	

	//拿到首页最热的电影，点赞数决定
	public Movie[] getMostLikeMovie() {
		Movie[] movies = new Movie[8];
		int i = 0;
		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT COUNT(movie_id) AS x, movie_id, movie_name, movie_image FROM click,movie WHERE movie.id=movie_id GROUP BY movie_id ORDER BY x desc LIMIT 8";
		 
		try {
			ps = co.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Movie movie = new Movie();
				movie.setId(rs.getInt("movie_id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieSumClick(rs.getInt("x"));
				movie.setMovieName(rs.getString("movie_name"));
				movies[i] = movie;
				i++;
			}
		} catch (SQLException e) { 
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return movies;
	}
	//拿到首页即将上映
	public Movie[] getHighestScoreMovie() {
		Movie[] movies = new Movie[8];
		int i = 0;
		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT  * FROM (SELECT AVG(grades),movie_id FROM givegrades GROUP BY movie_id ORDER BY AVG(grades) desc) AS X, movie WHERE movie.id=movie_id limit 8";
		try {
			ps = co.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Movie movie = new Movie();
				movie.setId(rs.getInt("movie_id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieGrades(rs.getDouble("avg(grades)"));
				movie.setMovieName(rs.getString("movie_name"));
				movies[i] = movie;
				i++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return movies;
	}	
	
	//拿到首页即将上映电影
	public Movie[] getComingInMovie() {
		Movie[] movies = new Movie[8];
		int i = 0;
		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT id,movie_name,movie_image,movie_showtime FROM movie WHERE movie_show=0 ORDER BY movie_showtime limit 8";
		try {
			ps = co.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Movie movie = new Movie();
				movie.setId(rs.getInt("id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieShowtime(rs.getTimestamp("movie_showtime"));
				movie.setMovieName(rs.getString("movie_name"));
				movies[i] = movie;
				i++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return movies;
	}
	
	public Movie movieGetMovieId(int movieId) {
		
		Connection co = DBHelper.getDBHelper().getconn();
		String sql="select * from movie,movie_country,movie_language,movie_type where movie.id=? and movie_country.id=movie_country_id and movie_language_id=movie_language.id and movie_type_id=movie_type.id";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Movie movie=new Movie();
		try {
			ps = co.prepareStatement(sql);
			ps.setInt(1,movieId );
			rs= ps.executeQuery();
			 while(rs.next()) {
				 movie.setId(movieId);//锟斤拷拥锟接癷d
				 movie.setMovieName(rs.getString("movie_name"));//锟斤拷拥锟接帮拷锟斤拷锟�
				 movie.setMovieTime(rs.getInt("movie_time"));//锟斤拷拥锟接笆憋拷锟�
				 //锟斤拷拥锟接帮拷锟斤拷锟�
				 MovieType  c=new MovieType();
				 c.setId(rs.getInt("movie_type_id"));
				 c.setMovieType(rs.getString("movie_type"));
				 movie.setMovieTypeId(c);
				 //锟斤拷拥锟接帮拷锟接筹拷锟斤拷锟�
				 movie.setMovieShowtime(rs.getTimestamp("movie_showtime"));
				 //锟斤拷拥锟接帮拷锟斤拷锟�
				 MovieCountry c2=new MovieCountry();
				 c2.setId(rs.getInt("movie_country_id"));
				 c2.setCountry(rs.getString("country"));
				 movie.setMovieCountryId(c2);
				 //锟斤拷拥锟接帮拷锟斤拷锟�
				 MovieLanguage c3=new MovieLanguage();
				 c3.setId(rs.getInt("movie_language_id"));
				 c3.setLanguageType(rs.getString("language_type"));
				 movie.setMovieLanguageId(c3);
				 //锟斤拷拥锟接帮拷锟斤拷锟酵�
				 movie.setMovieImage(rs.getString("movie_image"));
				 //锟斤拷拥锟接帮拷锟斤拷
				 movie.setMovieIntroduction(rs.getString("movie_introduction"));
				 //锟叫断碉拷影锟角凤拷锟斤拷映
				 movie.setMovieShow(rs.getBoolean("movie_show"));
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {

			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return movie;

	}

	
	public ArrayList<Collect> getUserCollects(int id) {
		ArrayList<Collect> collects = new ArrayList<>();
		 
		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM collect,movie WHERE user_id=? AND collect.movie_id=movie.id order by movie_showtime desc limit 0,5";
		try {
			ps = co.prepareStatement(sql);
			ps.setInt(1, id);
			//System.out.println(sql+"**********");
			//System.out.println(id+"***********");
			rs = ps.executeQuery();
			while (rs.next()) {
				Collect collect = new Collect();
				Movie movie = new Movie();
				movie.setId(rs.getInt("movie.id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieName(rs.getString("movie_name"));
				movie.setMovieShowtime(rs.getTimestamp("movie_showtime"));
				collect.setMovieId(movie);
				collect.setId(rs.getInt("collect.id"));
				collects.add(collect);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return collects;
	}
	public ArrayList<Movie> getMovieShowtimeList() {
		ArrayList<Movie> list = new ArrayList();
		
		Connection conn = DBHelper.getDBHelper().getconn();
		PreparedStatement ps =null;
		ResultSet rs =null;
		String sql ="select * from movie";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Movie m = new Movie();
				m.setId(rs.getInt("id"));
				m.setMovieShowtime(rs.getTimestamp("movie_showtime") );
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return list;
	}
	public ArrayList<Movie> getRecommendMovie() {
		ArrayList<Movie> list = new ArrayList();
		
		Connection conn = DBHelper.getDBHelper().getconn();
		PreparedStatement ps =null;
		ResultSet rs =null;
		String sql =" select * from movie order by movie_showtime desc";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Movie m = new Movie();
				m.setId(rs.getInt("id"));
				m.setMovieImage(rs.getString("movie_image"));
				m.setMovieName(rs.getString("movie_name"));
				list.add(m); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return list;
	}

	
	//添加电影

	public void addMovie(Movie movie) {
		Connection co = DBHelper.getDBHelper().getconn();
		StringBuilder sb = new StringBuilder();
		sb.append("  INSERT INTO movie(movie_name,movie_time,movie_language_id,movie_showtime,movie_country_id");
		sb.append(",movie_type_id,movie_show,movie_introduction,movie_image,movie_poster,maneger_id)   ");
		sb.append("  VALUES(?,?,?,?,?,?,?,?,?,?,?)  ");
		String sql = sb.toString();

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = co.prepareStatement(sql);
			ps.setString(1, movie.getMovieName());
			ps.setInt(2, movie.getMovieTime());
			ps.setInt(3, movie.getMovieLanguageId().getId());
			ps.setTimestamp(4, movie.getMovieShowtime());
			ps.setInt(5, movie.getMovieCountryId().getId());
			ps.setInt(6, movie.getMovieTypeId().getId());
			ps.setBoolean(7, movie.isMovieShow());
			ps.setString(8, movie.getMovieIntroduction());
			ps.setString(9, movie.getMovieImage());
			ps.setString(10, movie.getMoviePoster());
			ps.setInt(11, movie.getManegerid().getId());

			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}

	}

	// 寻找最近添加的一部电影
	public Movie getNewMovie(int manegerId) {

		Movie movie = new Movie();
		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select max(id) as c from movie where maneger_id=?";

		try {
			ps = co.prepareStatement(sql);
			ps.setInt(1, manegerId);

			rs = ps.executeQuery();
			rs.next();
			movie.setId(rs.getInt("c"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		{
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return movie;
	}
	public int getCountType(int MovieTypeId) {//给我一个类型的id查找出这个类型一共有多少部电影
		Connection co=DBHelper.getDBHelper().getconn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT COUNT(*) as c FROM movie WHERE movie_type_id=?";
		try {
			ps=co.prepareStatement(sql);
			ps.setInt(1, MovieTypeId);
			rs=ps.executeQuery();
			rs.next();
			int count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return 0;
	}
	  public ArrayList<Movie> getTypeMovie(MovieType movietype){//按照电影的类型展开电影，八个为一组
		  ArrayList<Movie>alltypemovie=new ArrayList<Movie>();
		  	Connection co=DBHelper.getDBHelper().getconn();
		  	PreparedStatement ps=null;
		  	ResultSet rs=null;
		    StringBuilder sb=new StringBuilder();
		    sb.append(" SELECT * FROM movie,movie_type WHERE movie_type_id=?  ");
		    sb.append(" ORDER BY movie_showtime DESC ");
		    sb.append(" LIMIT ?,? ");
		  	String sql=sb.toString();
		  	try {
				ps=co.prepareStatement(sql);
				ps.setInt(1, movietype.getId());
				ps.setInt(2, movietype.getPageNo());//从第几条开始查
				ps.setInt(3, movietype.getPagerow());//每条往后查几条
				rs=ps.executeQuery();
				while(rs.next()) {
					Movie movie=new Movie();
					movie.setId(rs.getInt("movie.id"));
					movie.setMovieImage(rs.getString("movie_image"));
					movie.setMovieName(rs.getString("movie_name"));
					alltypemovie.add(movie);
				}
				return alltypemovie;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		  	finally {
		  		DBHelper.getDBHelper().close(rs, ps, co);
		  	}
		  	return null;
	  }
	     public int getTimeMovie(String timemovie) {//给我一个年限查找出这个年限内有多少部电影
	    	 Connection co=DBHelper.getDBHelper().getconn();
	    	 PreparedStatement ps=null;
	    	 ResultSet rs=null;
	    	 String sql="SELECT COUNT(*) as m FROM movie WHERE year(movie_showtime)='?'";
	    	 try {
				ps=co.prepareStatement(sql);
				ps.setString(1, timemovie);
				rs=ps.executeQuery();
				rs.next();
				int count=rs.getInt("m");
				return count;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    	 finally {
	    		 DBHelper.getDBHelper().close(rs, ps, co);
	    	 }
	    	 return 0;
	     }
         public ArrayList<Movie> gettimemovie(String timestamp,MovieType movietype){//给我年限给你找出这个年限有哪些电影
        	 
        	ArrayList<Movie>alltimetype=new ArrayList<>();
        	Connection co= DBHelper.getDBHelper().getconn();
        	PreparedStatement ps=null;
        	ResultSet rs=null;
        	StringBuilder sb=new StringBuilder();
        	sb.append(" SELECT * FROM movie WHERE year(movie_showtime)='?' ");
        	sb.append(" AND  ");
        	sb.append(" movie_type_id=1  ");
        	sb.append(" ORDER BY movie_time DESC  ");
        	sb.append(" limit ?,? ");
        	String sql=sb.toString();
        	try {
				ps=co.prepareStatement(sql);
				ps.setString(1, timestamp);
				ps.setInt(2, movietype.getPageNo());//从第几条开始查
				ps.setInt(3, movietype.getPageSize());//往下查几条
				rs=ps.executeQuery();
				while(rs.next()) {
					Movie movie=new Movie();
					movie.setId(rs.getInt("id"));
					movie.setMovieImage(rs.getString("movie_image"));
					movie.setMovieName(rs.getString("movie_name"));
					alltimetype.add(movie);
				}
				return alltimetype;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	finally {
        		DBHelper.getDBHelper().close(rs, ps, co);
        	}
        	return null;
         }
	 public int gettimetypecount(String timestamp,int typeid){//给我年限和电影类型，找出符合要求的电影的数量
		 Connection co=DBHelper.getDBHelper().getconn();
		 PreparedStatement ps=null;
		 ResultSet rs=null;
		 StringBuilder sb=new StringBuilder();
		 sb.append(" SELECT COUNT(*) AS m  FROM movie WHERE year(movie_showtime)='?' ");
		 sb.append(" AND movie_type_id=? ");
		 String sql=sb.toString();
		 try {
			ps=co.prepareStatement(sql);
			ps.setString(1, timestamp);
			ps.setInt(2, typeid);
			rs=ps.executeQuery();
			rs.next();
			int count=rs.getInt("m");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally {
			 DBHelper.getDBHelper().close(rs, ps, co);
		 }
		 return 0;
	 }
	public ArrayList<Movie>alltypetime(MovieType movietype,String timestamp){//给我电影类型的id和年限，根据条件列出来符合条件的电影
		ArrayList<Movie>allmovie=new ArrayList<Movie>();
		Connection co=DBHelper.getDBHelper().getconn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT *  FROM movie WHERE year(movie_showtime)='?' ");
		sb.append(" AND movie_type_id=? ");
		sb.append(" ORDER BY movie_showtime DESC  ");
		sb.append(" LIMIT ?,? ");
		String sql="";
		try {
			ps=co.prepareStatement(sql);
			ps.setString(1, timestamp);//年限
			ps.setInt(2, movietype.getId());//类型
			ps.setInt(3, movietype.getPageNo());//从第几条开始查
			ps.setInt(4, movietype.getPageSize());//往后查几条
			rs=ps.executeQuery();
			while(rs.next()) {
				Movie movie=new Movie();
				movie.setId(rs.getInt("id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieName(rs.getString("movie_name"));
				allmovie.add(movie);
			}
			return allmovie;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		return null;
		
	}
	  public int alllikecount(String neirong){//根据模糊查找有多少条记录
		  Connection co=DBHelper.getDBHelper().getconn();
		  PreparedStatement ps=null;
		  ResultSet rs=null;
		  String sql="SELECT COUNT(id) AS m FROM movie WHERE movie_name LIKE '%"+neirong+"%'";
		  try {
			ps=co.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			int max=rs.getInt("m");
			return max;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		  finally {
			  DBHelper.getDBHelper().close(rs, ps, co);
		  }
		  return 0;
	  }
	  public ArrayList<Movie> alllikemovie(Movie movies,String neirong){
		  ArrayList<Movie>allmovie=new ArrayList<Movie>();
		  Connection co=DBHelper.getDBHelper().getconn();
	      PreparedStatement ps=null;
	      ResultSet rs=null;
	      StringBuilder sb=new StringBuilder();
	      sb.append(" SELECT * FROM ");
	      sb.append(" movie,movie_country,movie_language,movie_type ");
	      sb.append(" WHERE movie_name LIKE '%"+neirong+"%' and ");
	      sb.append(" movie_country.id=movie_country_id and ");
	      sb.append(" movie_language_id=movie_language.id and ");
	      sb.append(" movie_type_id=movie_type.id ");
	      sb.append(" ORDER BY movie_showtime DESC  ");
	      sb.append(" LIMIT ?,? ");
	      String sql=sb.toString();
	      try {
			ps=co.prepareStatement(sql);
			ps.setInt(1,movies.getPageNo() );
			ps.setInt(2,movies.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				 Movie movie=new Movie();
				 movie.setId(rs.getInt("movie.id"));//电影id
				 movie.setMovieName(rs.getString("movie_name"));//电影名
				 movie.setMovieTime(rs.getInt("movie_time"));//电影时长
				 
				 MovieType  c=new MovieType();//电影类型对象
				 c.setId(rs.getInt("movie_type_id"));//类型id
				 c.setMovieType(rs.getString("movie_type"));//类型名称
				 movie.setMovieTypeId(c);//将电影类型对象添加到电影对象
				 
				 movie.setMovieShowtime(rs.getTimestamp("movie_showtime"));//上映时间
				 
				 MovieCountry c2=new MovieCountry();//电影地区对象
				 c2.setId(rs.getInt("movie_country_id"));//地区id
				 c2.setCountry(rs.getString("country"));//地区名称
				 movie.setMovieCountryId(c2);//地区加到电影
				 
				 MovieLanguage c3=new MovieLanguage();//电影语言
				 c3.setId(rs.getInt("movie_language_id"));//语言id
				 c3.setLanguageType(rs.getString("language_type"));//语言类型
				 movie.setMovieLanguageId(c3);//语言加到电影
				 
				 movie.setMovieImage(rs.getString("movie_image"));//电影缩略图
				 movie.setMovieIntroduction(rs.getString("movie_introduction"));//电影简介
				 movie.setMovieShow(rs.getBoolean("movie_show"));//是否热映
				 allmovie.add(movie);
			}
			return allmovie;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  finally {
		  DBHelper.getDBHelper().close(rs, ps, co);
	  }
	  return null;
	  
	  
	  }
	  

	// 获得电影列表前8个
	public ArrayList<Movie> getMovieList( ) {

		ArrayList<Movie> list = new ArrayList<Movie>();

		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "select * from movie limit 0,8";
		try {
			ps = co.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Movie movie = new Movie();
				movie.setId(rs.getInt("id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieName(rs.getString("movie_name"));
				list.add(movie);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}

		return list;
	}

	// 通过名字查询电影列表

	public ArrayList<Movie> getMovieList(String m) {

		ArrayList<Movie> list = new ArrayList<Movie>();

		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "select * from movie where movie_name like '%" + m + "%'";
		try {
			ps = co.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Movie movie = new Movie();
				movie.setId(rs.getInt("id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieName(rs.getString("movie_name"));

				list.add(movie);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}

		return list;
	}

	// 通过名id删除电影

	public void deleteMovie(int m) {

		Connection co = DBHelper.getDBHelper().getconn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "delete from movie where id=? ";
		try {
			
			ps = co.prepareStatement(sql);
			ps.setInt(1, m);
			
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}

	}
	// 通过id查找电影
		public Movie getMovieById(int id) {

			Connection co = DBHelper.getDBHelper().getconn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			Movie movie = new Movie();
			String sql = "select *from movie where id=?";
			try {
				ps=co.prepareStatement(sql);
				
				ps.setInt(1, id);
				rs=ps.executeQuery();
				
				rs.next();
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieIntroduction(rs.getString("movie_introduction"));
				movie.setMovieName(rs.getString("movie_name"));
				movie.setMovieShow(rs.getBoolean("movie_show"));
				movie.setMoviePoster(rs.getString("movie_poster"));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return movie;
		}
		
		// 通过名id修改电影

		public void motifyMovie(int id,String movieName,String movieIntoduce,int movieShow) {

			Connection co = DBHelper.getDBHelper().getconn();
			PreparedStatement ps = null;
			ResultSet rs = null;

			String sql = "update movie set movie_name=?,movie_introduction=?,movie_show=? where id=? ";
			try {
				
				ps = co.prepareStatement(sql);
				ps.setString(1, movieName);
				ps.setString(2, movieIntoduce);
				ps.setInt(3, movieShow);
				ps.setInt(4, id);
				
				ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			}

		}

		// 通过名id修改电影

		public void motifyMovie(int id,String movieName,String movieIntoduce,int movieShow,String fileName) {

					Connection co = DBHelper.getDBHelper().getconn();
					PreparedStatement ps = null;
					ResultSet rs = null;

					String sql = "update movie set movie_name=?,movie_introduction=?,movie_show=?,movie_image=? where id=? ";
					try {
						
						ps = co.prepareStatement(sql);
						ps.setString(1, movieName);
						ps.setString(2, movieIntoduce);
						ps.setInt(3, movieShow);
						ps.setString(4, fileName);
						ps.setInt(5, id);
						
						ps.executeUpdate();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}

					finally {
						DBHelper.getDBHelper().close(rs, ps, co);
					}

				}
		
		
		public ArrayList<Movie> getMoviesWithTags(String type, String country, String language) {
			ArrayList<Movie> movies = new ArrayList<>();
			
			Connection co = DBHelper.getDBHelper().getconn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			StringBuilder sb = new StringBuilder();
			sb.append(" SELECT * FROM movie WHERE ");
			if (!"null".equals(type)) {
				sb.append(" movie_type_id=(SELECT id FROM movie_type WHERE `movie_type`=\"" + type + "\") and ");
			}
			if (!"null".equals(language)) {
				sb.append(" movie_language_id=(SELECT id FROM movie_language WHERE `language_type`=\"" + language + "\") and ");
			}
			if (!"null".equals(country)) {
				sb.append(" movie_country_id=(SELECT id FROM movie_country WHERE country=\"" + country + "\") and ");
			}
			if (!(type.equals(language) && type.equals(country))) {//不全都是null
			    String str = sb.substring(0, sb.length()-4);
			    sb.delete(0, sb.length());
			    sb.append(str);
			} 
			if (type.equals(language) && type.equals(country)) {//全都是null
			    String str = sb.substring(0, sb.length()-6);
			    sb.delete(0, sb.length());
			    sb.append(str);
			} 
			sb.append(" ORDER BY movie_showtime desc ");
			try {
				ps = co.prepareStatement(sb.toString());
				rs = ps.executeQuery();
				while (rs.next()) {
					Movie m = new Movie();
					m.setId(rs.getInt("id"));
					m.setMovieImage(rs.getString("movie_image"));
					m.setMovieName(rs.getString("movie_name"));
					movies.add(m); 
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			}
			return movies;
		}

		//点击通过导演id跳转页面
		public ArrayList<Movie> findMoviesByDirectorId(int id) {
			ArrayList<Movie> movies = new ArrayList<>();
			
			Connection co = DBHelper.getDBHelper().getconn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			String sql ="select * from movie,movie_director,director where director.id=? and movie_director.movie_id=movie.id and director_id=director.id";
			try {
				ps = co.prepareStatement(sql);
				ps.setInt(1, id);
				
				rs=ps.executeQuery();
			while(rs.next()) {
				Movie movie =new Movie();
				movie.setId(rs.getInt("movie.id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieName(rs.getString("movie_name"));
				movies.add(movie);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			 finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			 }
			return movies;
		}
		//点击通过演员id跳转页面
		public ArrayList<Movie> findMoviesByActorId(int id) {
			ArrayList<Movie> movies = new ArrayList<>();
			
			Connection co = DBHelper.getDBHelper().getconn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			String sql ="select * from movie,movie_actor,actor where actor.id=? and movie_actor.movie_id=movie.id and actor_id=actor.id";
			try {
				ps = co.prepareStatement(sql);
				ps.setInt(1, id);
				
				rs=ps.executeQuery();
			while(rs.next()) {
				Movie movie =new Movie();
				movie.setId(rs.getInt("movie.id"));
				movie.setMovieImage(rs.getString("movie_image"));
				movie.setMovieName(rs.getString("movie_name"));
				
				movies.add(movie);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			 }
			return movies;
		}
		public void addmovierecords(String moviename,int manegerid) {//增加电影的记录
			String sql="insert into add_movie_records(movie_name,maneger_id) VALUES(?,?)";
			DBHelper.getDBHelper().update(sql, moviename,manegerid);
		}
		public void deletemovierecords(String moviename,int manegerid) {//删除电影记录
			String sql="insert into delete_movie_records(movie_name,maneger_id) VALUES(?,?)";
			DBHelper.getDBHelper().update(sql, moviename,manegerid);
		}
		public void modfinmovieimagerecords(Movie movie,int manegerid) {//有照片的方法
			String sql="insert into modfin_movie_records(movie_name,maneger_id,movie_image,movie_id,movie_Introduction,check_bit) VALUES(?,?,?,?,?,?)";
			DBHelper.getDBHelper().update(sql,movie.getMovieName(),manegerid,movie.getMovieImage(),movie.getId(),movie.getMovieIntroduction(),movie.isMovieShow());
		}
        public void modfinmovierecords(Movie movie,int manegerid) {//没有照片的方法
        	String sql="insert into modfin_movie_records(movie_name,maneger_id,movie_id,movie_Introduction,check_bit) VALUES(?,?,?,?,?)";
			DBHelper.getDBHelper().update(sql,movie.getMovieName(),manegerid,movie.getId(),movie.getMovieIntroduction(),movie.isMovieShow());
		}
		public int addmovierecordsmax(int manegerid) {//此id管理员添加电影的操作共有多少条记录
			Connection co=DBHelper.getDBHelper().getconn();
			PreparedStatement ps=null;
			ResultSet rs=null;
			String sql="SELECT COUNT(*) AS m FROM add_movie_records where maneger_id=?";
			try {
				ps=co.prepareStatement(sql);
				ps.setInt(1, manegerid);
				rs=ps.executeQuery();
				rs.next();
				int m=rs.getInt("m");
				return m;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			}
			return 0;
		}
		public int deletemovierecordsmax(int manegerid) {//此id管理员删除电影的操作共有多少条记录
			Connection co=DBHelper.getDBHelper().getconn();
			PreparedStatement ps=null;
			ResultSet rs=null;
			String sql="SELECT COUNT(*) AS m FROM delete_movie_records where maneger_id=?";
			try {
				ps=co.prepareStatement(sql);
				ps.setInt(1, manegerid);
				rs=ps.executeQuery();
				rs.next();
				int m=rs.getInt("m");
				return m;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			}
			return 0;
		}
		public int ModfinMovieRecordsmax(int manegerid) {//此id管理员修改电影的操作共有多少条记录
			Connection co=DBHelper.getDBHelper().getconn();
			PreparedStatement ps=null;
			ResultSet rs=null;
			String sql="SELECT COUNT(*) AS m FROM modfin_movie_records where maneger_id=?";
			try {
				ps=co.prepareStatement(sql);
				ps.setInt(1, manegerid);
				rs=ps.executeQuery();
				rs.next();
				int m=rs.getInt("m");
				return m;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			}
			return 0;
		}
		public ArrayList<AddMovieRecords> addrecordscontent(int manegerid,int thispage) {//此id管理员添加电影的操作记录内容翻出去
			ArrayList<AddMovieRecords>addmovie=new ArrayList<AddMovieRecords>();
			Connection co=DBHelper.getDBHelper().getconn();
			PreparedStatement ps=null;
			ResultSet rs=null;
			String sql="SELECT * FROM add_movie_records where maneger_id=? limit ?,5 ";
			try {
				ps=co.prepareStatement(sql);
				ps.setInt(1, manegerid);
				ps.setInt(2, (thispage-1)*6);
				rs=ps.executeQuery();
				while(rs.next()) {
				AddMovieRecords add=new AddMovieRecords();
				add.setId(rs.getInt("id"));
				add.setTimestamp(rs.getTimestamp("records_time"));
			    add.setMoviename(rs.getString("movie_name"));
			    addmovie.add(add);
				}
				return addmovie;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getDBHelper().close(rs, ps, co);
			}
			return null;
		}
		public ArrayList<DeleteMovieRecords> deleterecordscontent(int manegerid,int thispage) {//删除电影的操作内容翻出来
			ArrayList<DeleteMovieRecords> deletemovie=new ArrayList<DeleteMovieRecords>();
			Connection co=DBHelper.getDBHelper().getconn();
		    PreparedStatement ps=null;
		    ResultSet rs=null;
		    String sql="SELECT * FROM delete_movie_records where maneger_id=? limit ?,6 ";
		    try {
				ps=co.prepareStatement(sql);
				
				 ps.setInt(1, manegerid);
				 ps.setInt(2, (thispage-1)*6);
				 rs=ps.executeQuery();
				 while(rs.next()) {
					 DeleteMovieRecords delete=new DeleteMovieRecords();
					 delete.setId(rs.getInt("id"));
					 delete.setMoviename(rs.getString("movie_name"));
					 delete.setTimestamp(rs.getTimestamp("records_time"));
					 deletemovie.add(delete);
				 }
				 return deletemovie;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		   finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		    return null;
		}
		public ArrayList<MonfinMovieRecords> ModfinRecordscontent(int manegerid,int thispage) {//修改电影的操作内容翻出来
			ArrayList<MonfinMovieRecords> modfinmovie=new ArrayList<MonfinMovieRecords>();
			Connection co=DBHelper.getDBHelper().getconn();
		    PreparedStatement ps=null;
		    ResultSet rs=null;
		    String sql="SELECT * FROM modfin_movie_records where maneger_id=? limit ?,6 ";
		    try {
				ps=co.prepareStatement(sql);
				 ps.setInt(1, manegerid);
				 ps.setInt(2, (thispage-1)*6);
				 rs=ps.executeQuery();
				 while(rs.next()) {
					 MonfinMovieRecords modfin=new MonfinMovieRecords();
					modfin.setCheckBit(rs.getBoolean("check_bit"));
					modfin.setId(rs.getInt("id"));
					modfin.setMovieImage(rs.getString("movie_image"));
					modfin.setMovieIntroduction(rs.getString("movie_introduction"));
					modfin.setMovieName(rs.getString("movie_name"));
					modfin.setCheckBit(rs.getBoolean("check_bit"));
					modfin.setTimestamp(rs.getTimestamp("modfin_time"));
					modfinmovie.add(modfin);
				 }
				 return modfinmovie;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		   finally {
			DBHelper.getDBHelper().close(rs, ps, co);
		}
		    return null;
		}
	
	public static ArrayList<Integer> getfen(){
		
		ArrayList<Integer> fen = new ArrayList<Integer>();
		Connection co=DBHelper.getDBHelper().getconn();
	    PreparedStatement ps=null;
	    ResultSet rs=null;
	    String sql="SELECT id FROM movie ";
	    try {
			ps =co.prepareStatement(sql);
			rs= ps.executeQuery();
			
			while(rs.next()) {
				fen.add(rs.getInt("id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	    finally {
	    	DBHelper.getDBHelper().close(rs, ps, co);
	    }
		return fen;
	}
		
   public static void fen(int id){
		
		
		Connection co=DBHelper.getDBHelper().getconn();
	    PreparedStatement ps=null;
	    ResultSet rs=null;
	    String sql="insert into givegrades(movie_id,grades,user_id) values(?,5,1)";
	    try {
			ps =co.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	    finally {
	    	DBHelper.getDBHelper().close(rs, ps, co);
	    }
		
	}
   public static void click(int id){
		
		
		Connection co=DBHelper.getDBHelper().getconn();
	    PreparedStatement ps=null;
	    ResultSet rs=null;
	    String sql="insert into click(movie_id,user_id) values(?,1)";
	    try {
			ps =co.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	    finally {
	    	DBHelper.getDBHelper().close(rs, ps, co);
	    }
		
	}
		 
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
}


